﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.WindowsAzure.ServiceRuntime;


namespace Repositories
{
	public class ChartRepository
	{

		string connStr;


		public ChartRepository()
		{
			//connStr = //System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
			connStr = RoleEnvironment.GetConfigurationSettingValue("ConnectionString");
		}



		public IList< System.Collections.Generic.KeyValuePair<int, int>> GetPostsPerHour()
		{
			//List<Forum> forums = new List<Forum>();
			IList<KeyValuePair<int, int>> result = new List<KeyValuePair<int, int>>();

			SqlConnection conn = new SqlConnection(connStr);

			try
			{
				
				SqlCommand cmd = new SqlCommand();
				cmd.Connection = conn;
				cmd.CommandText = "GetPostsPerHour";
				cmd.CommandType = CommandType.StoredProcedure;

				conn.Open();

				SqlDataReader dr = cmd.ExecuteReader();



				while (dr.Read())
				{
					KeyValuePair<int, int> kvp = new KeyValuePair<int,int>( Int16.Parse( dr[0].ToString()), Int16.Parse( dr[1].ToString()));
					result.Add(kvp);
				}

				conn.Close();

			}
			catch (SqlException ex)
			{
				conn.Close();
				conn.Dispose();
				conn = null;

				if (ex.Number == 2812)
				{
					//Stored procedure doesn't exist!
					throw new Exception("Stored procedure GetPostsPerHour doesn't exists", ex);
				}
				else if (ex.Number == 515)
				{
					//Know bug where SQL Server has closed the connection, but the connection is still available in the asp.net connection pool
					GetPostsPerHour();
				}
				else
				{
					throw ex;
				}
			}
			catch (Exception ex)
			{
				conn.Close();
				conn.Dispose();
				conn = null;

				throw ex;
			}


			return result;
		}











		public IList<System.Collections.Generic.KeyValuePair<string, int>> GetPostsPerWeekday()
		{
			//List<Forum> forums = new List<Forum>();
			IList<KeyValuePair<string, int>> result = new List<KeyValuePair<string, int>>();

			SqlConnection conn = new SqlConnection(connStr);

			try
			{
				
				SqlCommand cmd = new SqlCommand();
				cmd.Connection = conn;
				cmd.CommandText = "GetPostsPerWeekday";
				cmd.CommandType = CommandType.StoredProcedure;

				conn.Open();

				SqlDataReader dr = cmd.ExecuteReader();



				while (dr.Read())
				{
					KeyValuePair<string, int> kvp = new KeyValuePair<string, int>(dr[1].ToString(), Int16.Parse(dr[2].ToString()));
					result.Add(kvp);
				}

				conn.Close();

			}
			catch (SqlException ex)
			{
				conn.Close();
				conn.Dispose();
				conn = null;

				if (ex.Number == 2812)
				{
					//Stored procedure doesn't exist!
					throw new Exception("Stored procedure GetPostsPerWeekday doesn't exists", ex);
				}
				else if (ex.Number == 515)
				{
					//Know bug where SQL Server has closed the connection, but the connection is still available in the asp.net connection pool
					GetPostsPerHour();
				}
				else
				{
					throw ex;
				}
			}
			catch (Exception ex)
			{
				conn.Close();
				conn.Dispose();
				conn = null;

				throw ex;
			}


			return result;
		}

	}
}
